package unilearn.da;

import java.sql.*;
import java.util.Vector;
import javax.naming.*;

import unilearn.domain.Assignment;

/**
 * @author David Oliver, 4084551
 * @author Ben Morrall. 4158768
 * @version 0.1, April 30 2008, initial creation, dro
 * @version 0.2, April 30 2008, base insert/get functions added, dro
 * @version 0.3, April 30 2008, added delete/update functions, dro
 * @version 0.4, May 01 2008, added null check to ps var, blm
 * @version 0.5, May 01 2008, fixed bad sql query, blm
 * @version 0.6, May 14 2008, Code Refractor, blm
 */
public class DAAssignment extends DABase
{
	public static void deleteAssignment(long assignmentID) throws SQLException,
		NamingException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("DELETE FROM assignment WHERE assignment_id = ?");
			ps.setLong(1, assignmentID);

			ps.execute();
		}
		finally
		{
			if (ps != null) ps.close();
		}
	}

	private static Assignment generateAssignment(ResultSet rs) throws SQLException
	{
		Assignment assign = new Assignment(rs.getLong("assignment_id"));
		assign.setName(rs.getString("assignment_name"));
		assign.setDesc(rs.getString("assignment_description"));
		assign.setDueDate(rs.getTimestamp("due_date"));
		assign.setAcceptSubmissions(rs.getBoolean("accepting_submissions"));
		assign.setAcceptMarking(rs.getBoolean("accepting_marking"));
		assign.setReleaseDate(rs.getTimestamp("release_date"));
		assign.setSubjectID(rs.getLong("subject_id"));
		assign.setFileID(rs.getLong("file_id"));
		return assign;
	}

	public static Vector<Assignment> getAssignmentBySubject(long subjectID)
		throws SQLException, NamingException
	{
		Vector<Assignment> assigns = new Vector<Assignment>();
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("SELECT * FROM assignment WHERE subject_id = ?");
			ps.setLong(1, subjectID);

			ResultSet rs = ps.executeQuery();

			while (rs.next())
			{
				Assignment assign = generateAssignment(rs);
				assigns.add(assign);
			}

		}
		finally
		{
			if (ps != null) ps.close();
		}

		return assigns;
	}

	public static Assignment getAssignmentByID(long assignmentID) throws SQLException,
		NamingException
	{
		Assignment assign = null;
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("SELECT * FROM assignment WHERE assignment_id = ?");
			ps.setLong(1, assignmentID);

			ResultSet rs = ps.executeQuery();

			if (rs.next())
			{
				assign = generateAssignment(rs);
			}

		}
		finally
		{
			if (ps != null) ps.close();
		}

		return assign;
	}

	public static long insertAssignment(Assignment assign) throws SQLException,
		NamingException
	{
		long assID = 0;
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement(
					"INSERT INTO assignment "
							+ "(assignment_name,assignment_description,due_date,accepting_submissions,"
							+ "accepting_marking,release_date,subject_id,file_id) "
							+ "VALUES (?,?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);

			ps.setString(1, assign.getName());
			ps.setString(2, assign.getDesc());
			ps.setTimestamp(3, assign.getDueDate());
			ps.setBoolean(4, assign.getAcceptSubmissions());
			ps.setBoolean(5, assign.getAcceptMarking());
			ps.setTimestamp(6, assign.getReleaseDate());
			ps.setLong(7, assign.getSubjectID());
			ps.setLong(8, assign.getFileID());

			ps.execute();

			ResultSet results = ps.getGeneratedKeys();
			if (results.next())
			{
				assID = results.getLong(1);
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return assID;
	}

	public static void updateAssignment(long assignmentID, Assignment assign)
		throws SQLException, NamingException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("UPDATE assignment SET assignment_name = ?, assignment_description = ?, due_date = ?, "
					+ "accepting_submissions = ?, accepting_marking = ?, release_date = ?, subject_id = ? , file_id = ? "
					+ "WHERE assignment_id = ?");

			ps.setString(1, assign.getName());
			ps.setString(2, assign.getDesc());
			ps.setTimestamp(3, assign.getDueDate());
			ps.setBoolean(4, assign.getAcceptSubmissions());
			ps.setBoolean(5, assign.getAcceptMarking());
			ps.setTimestamp(6, assign.getReleaseDate());
			ps.setLong(7, assign.getSubjectID());
			ps.setLong(8, assign.getFileID());

			ps.execute();

		}
		finally
		{
			if (ps != null) ps.close();
		}
	}

	/**
	 * Prevent Instances of DAAssignment
	 */
	private DAAssignment()
	{
	}

}
